{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 合并excel文件到单个"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "direcory = \"销售表\"\n",
    "df_list = []\n",
    "for file in os.listdir(direcory):\n",
    "    if file.endswith(\".xlsx\"):\n",
    "        df_list.append(pd.read_excel(f\"{direcory}/{file}\"))\n",
    "df_all = pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>单号</th>\n",
       "      <th>产品名称</th>\n",
       "      <th>成本价（元/个）</th>\n",
       "      <th>销售价（元/个）</th>\n",
       "      <th>销售数量（个）</th>\n",
       "      <th>产品成本（元）</th>\n",
       "      <th>销售收入（元）</th>\n",
       "      <th>销售利润（元）</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>6123041</td>\n",
       "      <td>行李箱</td>\n",
       "      <td>22</td>\n",
       "      <td>88</td>\n",
       "      <td>870</td>\n",
       "      <td>19140</td>\n",
       "      <td>76560</td>\n",
       "      <td>57420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6123004</td>\n",
       "      <td>背包</td>\n",
       "      <td>16</td>\n",
       "      <td>65</td>\n",
       "      <td>230</td>\n",
       "      <td>3680</td>\n",
       "      <td>14950</td>\n",
       "      <td>11270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6123006</td>\n",
       "      <td>行李箱</td>\n",
       "      <td>22</td>\n",
       "      <td>88</td>\n",
       "      <td>850</td>\n",
       "      <td>18700</td>\n",
       "      <td>74800</td>\n",
       "      <td>56100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         单号 产品名称  成本价（元/个）  销售价（元/个）  销售数量（个）  产品成本（元）  销售收入（元）  销售利润（元）\n",
       "11  6123041  行李箱        22        88      870    19140    76560    57420\n",
       "1   6123004   背包        16        65      230     3680    14950    11270\n",
       "1   6123006  行李箱        22        88      850    18700    74800    56100"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计每个产品销售利润的总和、最大、最小、平均"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>总和</th>\n",
       "      <th>最小</th>\n",
       "      <th>最大</th>\n",
       "      <th>平均</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>产品名称</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>单肩包</th>\n",
       "      <td>317460.0</td>\n",
       "      <td>13200.0</td>\n",
       "      <td>51480.0</td>\n",
       "      <td>35273.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>手提包</th>\n",
       "      <td>450660.0</td>\n",
       "      <td>27750.0</td>\n",
       "      <td>98790.0</td>\n",
       "      <td>56332.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>背包</th>\n",
       "      <td>322910.0</td>\n",
       "      <td>7350.0</td>\n",
       "      <td>39200.0</td>\n",
       "      <td>24839.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>行李箱</th>\n",
       "      <td>483780.0</td>\n",
       "      <td>13860.0</td>\n",
       "      <td>57420.0</td>\n",
       "      <td>40315.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>钱包</th>\n",
       "      <td>468510.0</td>\n",
       "      <td>34920.0</td>\n",
       "      <td>97000.0</td>\n",
       "      <td>58563.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            总和       最小       最大        平均\n",
       "产品名称                                      \n",
       "单肩包   317460.0  13200.0  51480.0  35273.33\n",
       "手提包   450660.0  27750.0  98790.0  56332.50\n",
       "背包    322910.0   7350.0  39200.0  24839.23\n",
       "行李箱   483780.0  13860.0  57420.0  40315.00\n",
       "钱包    468510.0  34920.0  97000.0  58563.75"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def compute_data(df_sub):\n",
    "    return pd.Series({\n",
    "        \"总和\": round(df_sub[\"销售利润（元）\"].sum(), 2),\n",
    "        \"最小\": round(df_sub[\"销售利润（元）\"].min(), 2),\n",
    "        \"最大\": round(df_sub[\"销售利润（元）\"].max(), 2),\n",
    "        \"平均\": round(df_sub[\"销售利润（元）\"].mean(), 2),\n",
    "    })\n",
    "    \n",
    "df_group = df_all.groupby(\"产品名称\").apply(compute_data)\n",
    "\n",
    "df_group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 输出结果Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_group.to_excel(\"按产品汇总结果.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
